
# 读取年月数据
source("./code/2.3 人口数估发病率.R")

# 5.时空扫描数据准备------
case <- read_excel("data/origin/st/case.xlsx")
pop <- read_excel("data/origin/st/pop.xlsx")
geo <- read_excel("data/origin/st/geo_副本.xlsx") %>% 
  dplyr::select(`location ID`,newid = new_location_id,学生原始ID)

# 5.1病例数据准备------

case_processed <- case_month_long %>%
  # 1. 重命名count列为cases
  rename(cases = count) %>% 
  # 2. 筛选区县数据
  filter(category %in% "county") %>% 
  # 3. 重命名subgroup列为location ID
  rename(newid = subgroup) %>% 
  # 4. 创建日期变量
  mutate(
    date = paste(year, sprintf("%02d", month), sep = "/") # 保证月份补零
  ) %>% 
  # 5. 保留所需变量
  select(date, newid, cases,year,month) %>% left_join(geo)


# 创建目标文件夹（自动递归创建）
dir.create("data/origin/st_new", recursive = TRUE, showWarnings = FALSE)

# 定义阶段划分函数
# split_data <- function(data, start, end) {
#   data %>%
#     filter(year >= start, year <= end) %>%
#     select(date, `location ID` = newid, cases)
# }

split_data <- function(data, start, end) {
  data %>%
    filter(year >= start, year <= end) %>%
    select(date, `location ID` = 学生原始ID, cases) %>% 
    arrange(`location ID`,date)
}

# 分阶段处理数据
stg_2005_2009 <- split_data(case_processed, 2005, 2009)
stg_2010_2015 <- split_data(case_processed, 2010, 2015)
stg_2016_2019 <- split_data(case_processed, 2016, 2019)
stg_2020_2023 <- split_data(case_processed, 2020, 2023)

# 导出Excel文件
write_xlsx(stg_2005_2009, file.path("data/origin/st_new", "cases2005-2009.xlsx"))
write_xlsx(stg_2010_2015, file.path("data/origin/st_new", "cases2010-2015.xlsx"))
write_xlsx(stg_2016_2019, file.path("data/origin/st_new", "cases2016-2019.xlsx"))
write_xlsx(stg_2020_2023, file.path("data/origin/st_new", "cases2020-2023.xlsx"))


# 按年度导出数据（2005-2023）
for (y in 2005:2023) {
  # 生成年度数据
  annual_data <- split_data(case_processed, y, y)
  
  # 生成带年份的动态文件名（如 cases2005.xlsx）
  filename <- paste0("cases", y, ".xlsx")
  
  # 保存到指定路径
  write_xlsx(annual_data, file.path("data/origin/st_new", filename))
  
  # 打印进度提示
  message("已生成 ", y, " 年度数据文件：", filename)
}

# 5.2人口数据准备------
pop <- read_excel("data/origin/st/pop.xlsx")

pop_processed <- pop_long_expanded %>%
  # 1. 重命名value列为pop
  rename(pop = value) %>% 
  # 2. 筛选区县数据
  filter(region == "county") %>% 
  # 3. 重命名地区编码列为location ID
  rename(newid = 地区编码) %>% 
  # 4. 创建日期变量
  mutate(
    pop = round(pop, 0),
    date = paste(year, sprintf("%02d", month), sep = "/") # 保证月份补零
  ) %>% 
  # 5. 保留所需变量
  select(date, newid, pop,year,month) %>% left_join(geo)

# 定义阶段切割函数
split_pop_data <- function(data, start, end) {
  data %>%
    filter(year >= start & year <= end) %>%
    select(date, `location ID`  = 学生原始ID,  pop)%>% 
    arrange(`location ID`,date)
}
# 
# split_pop_data <- function(data, start, end) {
#   data %>%
#     filter(year >= start & year <= end) %>%
#     select(date, `location ID` = newid,  pop)
# }

# 分阶段处理数据
pop_2005_2009 <- split_pop_data(pop_processed, 2005, 2009)
pop_2010_2015 <- split_pop_data(pop_processed, 2010, 2015)
pop_2016_2019 <- split_pop_data(pop_processed, 2016, 2019)
pop_2020_2023 <- split_pop_data(pop_processed, 2020, 2023)

# 导出Excel文件
write_xlsx(pop_2005_2009, "data/origin/st_new/pop2005-2009_.xlsx")
write_xlsx(pop_2010_2015, "data/origin/st_new/pop2010-2015.xlsx")
write_xlsx(pop_2016_2019, "data/origin/st_new/pop2016-2019.xlsx")
write_xlsx(pop_2020_2023, "data/origin/st_new/pop2020-2023.xlsx")

# 按年度导出人口数据（2005-2023）
for (y in 2005:2023) {
  # 生成年度数据（复用split_pop_data函数）
  annual_pop <- split_pop_data(pop_processed, y, y)
  
  # 生成动态文件名（格式：pop2005.xlsx）
  filename <- paste0("pop", y, ".xlsx")
  
  # 保存到指定路径
  write_xlsx(annual_pop, file.path("data/origin/st_new", filename))
  
  # 打印进度提示
  message("已生成 ", y, " 年度人口数据文件：", filename)
}

# 5.3地理数据准备------
geo_new <- read_excel("data/origin/st/geo_副本.xlsx") %>%
  dplyr::select(`location ID`=学生原始ID,longitude,latitude)
# 
# geo_new <- read_excel("data/origin/st/geo_副本.xlsx") %>% 
#   dplyr::select(`location ID`=new_location_id,longitude,latitude)

write_xlsx(geo_new, "data/origin/st_new/geo_new.xlsx")
